package com.hwua.serviceimpl;

import com.hwua.dao.CarDao;
import com.hwua.dao.UserDao;
import com.hwua.daoimpl.CarDaoImpl;
import com.hwua.daoimpl.UserDaoImpl;
import com.hwua.domain.Car;
import com.hwua.domain.User;
import com.hwua.service.CarService;
import com.hwua.util.*;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

/**
 * 租车业务的实现类
 * @author Administrator
 */
public class CarServiceImpl implements CarService {
    /**
     * 全局变量，保存连接对象
     */
    private Connection connection;
    /**
     * 一、根据汽车编号租车
     *首先查看当前汽车状态是否已经出租，然后向租车记录表中增加一条数据，修改汽车表中汽车的状态为1，最后查询需要打印
     * 给用户看的租车信息
     * @param carId 汽车编号
     * @param userName 用户名称
     * @return
     */
    @Override
    public List<Car> rentCarById(String userName,int carId){
        List<Car> carList = new ArrayList<>();
        int result;
        try {
            //获取当前租车时间
            String rentTime = GetDateTools.getTime();
            //获取连接工具对象
            ConnectionTools ct = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            connection= ct.getConnection();
            //获取carDao对象，并将获取到的连接传入
            CarDao cd = new CarDaoImpl(connection);
            //调用query方法，根据传入的汽车id 查找当前汽车的出租状态
            List<Car> cars = cd.query("select car_rent_status from car where car_id=?", rSet -> {
                List<Car> list = new ArrayList<>();
                while (rSet.next()) {
                    Car car = new Car();
                    car.setRentStatus(rSet.getInt(TableInformationTools.Car.CAR_RENT_STATUS));
                    list.add(car);
                }
                return list;
            }, carId);
            int rentStatus=0;
            //对集合遍历，输出汽车当前出租状态
            for (Car car : cars) {
               rentStatus=car.getRentStatus();
            }
            //判断出租状态是否为0   （0代表可租，1代表不可租）
            if(rentStatus==0){
                //调用update方法，向租车记录表插入一条数据,返回受影响的行数
                result=cd.update("insert into c_record(record_id,car_id,rent_time,user_name) " +
                        " values(record_id_seq.nextval,?,to_date(?,'yyyy-MM-dd'),?)",carId,rentTime,userName);
                //判断上面的插入数据是否成功，如果成功向租车记录表插入一条数据，则继续执行修改和查询语句，否则提示租车失败异常
                if(result>0){
                    //调用update方法,修改car表中对应id 汽车的租赁状态为1（已出租）
                    cd.update("update car set car_rent_status=1 where car_id=?",carId);
                    //调用query方法，利用多表查询（car+c_brand+c_type+c_record）输出租车信息
                    carList=cd.query("select * from(select record_id,car_name,car_daily_rent,brand_name,type_name,car_remark,rent_time " +
                                                            " from car c" +
                                                            " full join c_brand cb" +
                                                            " on c.brand_id = cb.brand_id" +
                                                            " full join c_type ct" +
                                                            " on c.type_id = ct.type_id" +
                                                            " full join c_record cr" +
                                                            " on c.car_id = cr.car_id" +
                                                            " where cr.user_name=? and c.car_id = ?" +
                                                            " order by record_id desc)where rownum=1",rSet -> {
                        List<Car> list = new ArrayList<>();
                        while(rSet.next()){
                            Car car = new Car();
                            car.setRecordId(rSet.getInt(TableInformationTools.Car.RECORD_ID));
                            car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                            car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                            car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                            car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                            car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                            car.setRentTime(rSet.getString(TableInformationTools.Car.RENT_TIME));
                            list.add(car);
                        }
                        return list;
                    },userName,carId);
                }else{
                    System.out.println("租车失败，请重新操作！");
                }
            }else{
                System.out.println("这辆车已经被人租了，请重新选择！");
            }
            /*//关闭连接
            ct.closeConnection(connection);*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList ;
    }
    /**
     * 二、按照租车价格降序/升序排列
     *@param num 表示从键盘录入的数字，1表示降序，2表示升序
     */
    @Override
    public List<Car> sortByPrice(int num) {
        List<Car> carList=null;
        String sql="";
        try {
            //获取连接工具类
            ConnectionTools ct = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = ct.getConnection();
            //获取Dao对象并将连接传入
            CarDao cd = new CarDaoImpl(connection);
            //判断键盘录入+号后的数字，1代表按价格降序，2代表按价格升序
            if(num==1){
                sql = "select car_id,car_name,brand_name,type_name,car_color," +
                        " car_remark,car_daily_rent,car_rent_status,car_putaway_status" +
                        " from car c" +
                        " full join c_brand cb " +
                        " on c.brand_id = cb.brand_id" +
                        " full join c_type ct" +
                        " on c.type_id = ct.type_id" +
                        " order by c.car_daily_rent desc";
            }else if(num==2){
                sql = "select car_id,car_name,brand_name,type_name,car_color," +
                        " car_remark,car_daily_rent,car_rent_status,car_putaway_status" +
                        " from car c" +
                        " full join c_brand cb " +
                        " on c.brand_id = cb.brand_id" +
                        " full join c_type ct" +
                        " on c.type_id = ct.type_id" +
                        " order by c.car_daily_rent";
            }
            //调用查询方法，传入sql语句，按价格降序
            carList = cd.query(sql, rSet -> {
                List<Car> list = new ArrayList<>();
                try {
                    while (rSet.next()) {
                        Car car = new Car();
                        car.setCarId(rSet.getInt(TableInformationTools.Car.CAR_ID));
                        car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                        car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                        car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                        car.setColor(rSet.getString(TableInformationTools.Car.CAR_COLOR));
                        car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                        car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                        car.setRentStatus(rSet.getInt(TableInformationTools.Car.CAR_RENT_STATUS));
                        car.setPutAwayStatus(rSet.getInt(TableInformationTools.Car.CAR_PUTAWAY_STATUS));
                        list.add(car);
                    }
                } catch (NullPointerException e) {
                    e.printStackTrace();
                }
                return list;
            });

           /* //关闭连接
            ct.closeConnection(connection);*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList;
    }

    /**
     * 三、根据ID查找汽车
     *
     * @param id  可以是汽车id 类型id 品牌id
     * @param sign 作为标记，表面传入的是汽车id 、类型id 还是品牌id
     * @return
     */
    @Override
    public List<Car> findCarById(int id,String sign) {
        List<Car> carList=null;
        String sql = "";
        try {
            //获取连接工具对象
            ConnectionTools ct = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            connection = ct.getConnection();
            //获取carDao对象，并将获取到的连接传入
            CarDao cd = new CarDaoImpl(connection);
            if("类型id".equals(sign)){
                sql = "select car_id,car_name,brand_name,type_name,car_color," +
                        " car_remark,car_daily_rent,car_rent_status,car_putaway_status" +
                        " from car c" +
                        " full join c_brand cb" +
                        " on c.brand_id = cb.brand_id" +
                        " full join c_type ct" +
                        " on c.type_id = ct.type_id" +
                        " where c.type_id=?";
            }else if("品牌id".equals(sign)){
                sql = "select car_id,car_name,brand_name,type_name,car_color," +
                        " car_remark,car_daily_rent,car_rent_status,car_putaway_status" +
                        " from car c" +
                        " full join c_brand cb " +
                        " on c.brand_id = cb.brand_id" +
                        " full join c_type ct" +
                        " on c.type_id = ct.type_id"+
                        " where c.brand_id=?";
            }else if ("汽车id".equals(sign)){
                sql= "select car_id,car_name,brand_name,type_name,car_color," +
                        " car_remark,car_daily_rent,car_rent_status,car_putaway_status" +
                        " from car c" +
                        " full join c_brand cb " +
                        " on c.brand_id = cb.brand_id" +
                        " full join c_type ct" +
                        " on c.type_id = ct.type_id"+
                        " where c.car_id=?";
            }
            //调用query方法
            carList = cd.query(sql, rSet -> {
                List<Car> list = new ArrayList<>();
               try{
                   while (rSet.next()) {
                       Car car = new Car();
                       car.setCarId(rSet.getInt(TableInformationTools.Car.CAR_ID));
                       car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                       car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                       car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                       car.setColor(rSet.getString(TableInformationTools.Car.CAR_COLOR));
                       car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                       car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                       car.setRentStatus(rSet.getInt(TableInformationTools.Car.CAR_RENT_STATUS));
                       car.setPutAwayStatus(rSet.getInt(TableInformationTools.Car.CAR_PUTAWAY_STATUS));
                       list.add(car);
                   }
               }catch (NullPointerException e){
                    e.printStackTrace();
               }
                return list;
            },id);
           /* //关闭连接
            ct.closeConnection(connection);*/
        } catch (Exception e) {
           e.printStackTrace();
        }
        return carList;
    }

    /**
     * 四、查看所有汽车
     *
     * @return
     */
    @Override
    public List<Car> findAllCar() {
        List<Car> carList=null;
        try {
            //获取连接工具对象
            ConnectionTools ct = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            connection = ct.getConnection();
            CarDao cd = new CarDaoImpl(connection);

            carList = cd.query("select car_id,car_name,brand_name,type_name,car_color," +
                                    " car_remark,car_daily_rent,car_rent_status,car_putaway_status" +
                                    " from car c" +
                                    " full join c_brand cb " +
                                    " on c.brand_id = cb.brand_id" +
                                    " full join c_type ct" +
                                    " on c.type_id = ct.type_id" +
                                    " order by car_id", rSet -> {
                List<Car> list = new ArrayList<>();
                while(rSet.next()){
                    Car car = new Car();
                    car.setCarId(rSet.getInt(TableInformationTools.Car.CAR_ID));
                    car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                    car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                    car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                    car.setColor(rSet.getString(TableInformationTools.Car.CAR_COLOR));
                    car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                    car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                    car.setRentStatus(rSet.getInt(TableInformationTools.Car.CAR_RENT_STATUS));
                    car.setPutAwayStatus(rSet.getInt(TableInformationTools.Car.CAR_PUTAWAY_STATUS));
                    list.add(car);
                }
                return list;
            });
            /*//关闭连接
            ct.closeConnection(connection);*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList;
    }
    /**
     * 五、查看用户自己的租车记录
     *@return
     */
    @Override
    public List<Car> findRecordByUser(String userName) {
        List<Car> carList=null;
        try {
            //获取连接工具类对象
            ConnectionTools tools = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = tools.getConnection();
            //获取dao对象，并将连接传入
            CarDao cd = new CarDaoImpl(connection);
            //调用query方法，查询c_record中的租出信息
            carList = cd.query("select record_id,cr.car_id,car_name,car_daily_rent,payment,car_remark,brand_name,type_name,rent_time,return_time" +
                    " from car c full join c_record cr on c.car_id=cr.car_id" +
                    " full join c_brand cb on c.brand_id=cb.brand_id" +
                    " full join c_type ct on c.type_id=ct.type_id" +
                    " where cr.user_name=?" +
                    " order by record_id", rSet -> {
                List<Car> list = new ArrayList<>();
                while (rSet.next()) {
                    Car car = new Car();
                    car.setRecordId(rSet.getInt(TableInformationTools.Car.RECORD_ID));
                    car.setCarId(rSet.getInt(TableInformationTools.Car.CAR_ID));
                    car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                    car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                    car.setPayment(rSet.getDouble(TableInformationTools.Car.PAYMENT));
                    car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                    car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                    car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                    car.setRentTime(rSet.getString(TableInformationTools.Car.RENT_TIME));
                    car.setReturnTime(rSet.getString(TableInformationTools.Car.RETURN_TIME));
                    list.add(car);
                }
                return list;
            }, userName);
            /*//关闭连接
            tools.closeConnection(connection);*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList;
    }
    /**
     * 六、根据汽车编号还车
     *      1.如果有借车记录则向记录表添加还车时间和租车费用
     *      2.修改汽车表中对应编号的汽车状态为可租 3.多表查询打印需要输出的还车信息
     * @param userName 传入的当前用户名
     * @param carId 汽车编号
     * @param choose 是否选择打折
     * @return
     */
    @Override
    public List<Car> returnCarById(String userName,int carId,int choose) {
        String rentTime=null;
        double dailyRent=0;
        //声明一个总租车费用
        double payment=0;
        List<Car> carList1=null;
        try {
            //获取当前还车时间
            String returnTime = GetDateTools.getTime();
            //获取连接工具对象
            ConnectionTools tools = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = tools.getConnection();
            //获取userDao对象，传入对象
            UserDao us = new UserDaoImpl(connection);
            //调用query方法查询当前用户的等级user_level
            List<User> userList = us.query("select user_level from c_user where user_name=?", rSet -> {
                List<User> list = new ArrayList<>();
                while (rSet.next()) {
                    User user = new User();
                    user.setUserLevel(rSet.getInt(TableInformationTools.User.USER_LEVEL));
                    list.add(user);
                }
                return list;
            }, userName);
            int userLevel=1;
            for (User user : userList) {
                userLevel = user.getUserLevel();
            }
            //设定不同等级对应的折扣
            double discount = 0;
            switch (userLevel){
                case 1:discount=1;
                    break;
                case 2:discount=0.95;
                    break;
                case 3:discount=0.9;
                    break;
                case 4:discount=0.85;
                    break;
                case 5:discount=0.8;
                    break;
                default:break;
            }
            //获取carDao对象，并传入连接
            CarDao cd = new CarDaoImpl(connection);
            //调用query方法，根据传入的汽车id 查找当前汽车的出租状态
            List<Car> cars = cd.query("select car_rent_status from car where car_id=?", rSet -> {
                List<Car> list = new ArrayList<>();
                while (rSet.next()) {
                    Car car = new Car();
                    car.setRentStatus(rSet.getInt(TableInformationTools.Car.CAR_RENT_STATUS));
                    list.add(car);
                }
                return list;
            }, carId);
            int rentStatus=0;
            //对集合遍历，输出汽车当前出租状态
            for (Car car : cars) {
                rentStatus=car.getRentStatus();
            }
            //判断当前汽车是否是不可租状态1，不可租代表可以归还
            if(rentStatus==1){
                //调用query方法，查询当前用户所借对应汽车的开始时间rentTime 和 每日费用DailyRent(封装在car对象里面通过集合返回)
                List<Car> carList = cd.query("select rent_time,car_daily_rent from car c" +
                        " full join c_record cr on c.car_id=cr.car_id " +
                        " where user_name=? and cr.car_id=? and return_time is null ", rSet -> {
                    List<Car> list = new ArrayList<>();
                    while (rSet.next()) {
                        Car car = new Car();
                        car.setRentTime(rSet.getString(TableInformationTools.Car.RENT_TIME));
                        car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                        list.add(car);
                    }
                    return list;
                }, userName, carId);
                for (Car car : carList) {
                    rentTime = car.getRentTime();
                    dailyRent = car.getDailyRent();
                }
                //调用CalculateTimeTools工具，计算天数差
                CalculateTimeTools ctt = new CalculateTimeTools(rentTime,returnTime);
                int i = ctt.calculateTimeDifference();
                //判断天数差，不足一天的按1天算
                if(i==0){
                    i=1;
                }
                //判断从还车界面传入的用户选择是 1 选择打折 还是2 不打折
                if(choose==1){
                    //计算总费用
                    payment=dailyRent*i*discount;
                    System.out.println("==============================================================================");
                    System.out.println("您是"+userLevel+"级VIP用户，享受"+discount+"折优惠，实际支付："+payment+"元。");
                    System.out.println("==============================================================================");
                }else if(choose==2){
                    payment=dailyRent*i;
                }
                //调用update方法。修改租车记录表中该用户名和汽车id对应的记录。加上还车时间和租金,返回受影响的行数
                cd.update("update c_record set return_time=to_date(?,'yyyy-MM-dd'),payment=? where user_name=? and car_id=?" +
                        " and return_time is null", returnTime, payment, userName, carId);
                //调用update方法,修改car表中对应id 汽车的租赁状态为0（可租）
                int result =cd.update("update car set car_rent_status=0 where car_id=?",carId);
                //判断是否还车成功
                //调用query方法，查询需要打印输出的还车信息
                carList1 = cd.query("select * from(select record_id,car_name,car_daily_rent,payment,brand_name,type_name,car_color,car_remark,rent_time,return_time" +
                        " from car c" +
                        " full join c_brand cb" +
                        " on c.brand_id = cb.brand_id" +
                        " full join c_type ct" +
                        " on c.type_id = ct.type_id" +
                        " full join c_record cr" +
                        " on c.car_id = cr.car_id" +
                        " where cr.user_name=? and c.car_id = ?" +
                        " order by record_id desc) where rownum=1", rSet -> {
                    List<Car> list = new ArrayList<>();
                    while (rSet.next()) {
                        Car car = new Car();
                        car.setRecordId(rSet.getInt(TableInformationTools.Car.RECORD_ID));
                        car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                        car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                        car.setPayment(rSet.getDouble(TableInformationTools.Car.PAYMENT));
                        car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                        car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                        car.setColor(rSet.getString(TableInformationTools.Car.CAR_COLOR));
                        car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                        car.setRentTime(rSet.getString(TableInformationTools.Car.RENT_TIME));
                        car.setReturnTime(rSet.getString(TableInformationTools.Car.RETURN_TIME));
                        list.add(car);
                    }
                    return list;
                }, userName, carId);
                //调用本类中查找用户自己的租车记录方法获取租车次数和租金总额
                List<Car> list = findRecordByUser(userName);
                //获取集合长度表示租车次数
                int rentCount = list.size();
                //声明一个变量存放累计租金
                double totalPanment=0;
                //遍历集合，获取每次租金
                for (Car car : list) {
                    double carPayment = car.getPayment();
                    totalPanment+=carPayment;
                }
                //调用等级提升工具方法
                LevelUpTools.levelUp(userName,rentCount,totalPanment);
            }else{
                System.out.println("这辆车已经归还了！");
            }
           /* //关闭连接
            tools.closeConnection(connection);*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList1;
    }
    /**
     * 七、添加汽车
     *@param brandId
     * @param typeId
     * @param carName
     * @param carNumber
     * @param carRemark
     * @param color
     * @param price
     * @param dailyRent
     * @param rentStatus
     * @param putAwayStatus
     * @return
     */
    @Override
    public boolean addCar(int brandId, int typeId, String carName, String carNumber, String carRemark,
                          String color, long price, double dailyRent, int rentStatus, int putAwayStatus) {
        int result=0;
        try {
            //获取连接对象
            ConnectionTools tools = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = tools.getConnection();
            //获取dao对象，并传入连接
            CarDao cd = new CarDaoImpl(connection);
            //调用update方法 向汽车表中插入数据
            result = cd.update("insert into car values(car_id_seq.nextval,?,?,?,?,?,?,?,?,?,?)", carNumber, carName,
                    brandId, typeId, color, carRemark, dailyRent, price, rentStatus, putAwayStatus);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result>0;
    }
    /**
     * 八、根据汽车编号修改汽车每日租金
     *@param id            汽车id
     * @param dailyRent     每日租金
     * @return
     */
    @Override
    public List<Car> alterDailyRentById(int id, double dailyRent) {
        List<Car> carList=null;
        try {
            //获取连接对象
            ConnectionTools tools = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = tools.getConnection();
            //获取dao对象，并传入连接
            CarDao cd = new CarDaoImpl(connection);
            //调用update方法，修改每日租金
            int result = cd.update("update car set car_daily_rent = ? where car_id = ?", dailyRent, id);
            //判断是否修改成功
            if(result>0){
                //调用本类中findCarById方法查询修改成功的数据并返回打印
                carList = findCarById(id, "汽车id");
            }else{
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList;
    }
    /**
     * 九、根据汽车编号修改汽车上架状态
     *@param id            汽车id
     * @param putAwayStatus    上架状态
     * @return
     */
    @Override
    public List<Car> alterPutAwayStatusById(int id, int putAwayStatus) {
        List<Car> carList=null;
        try {
            //获取连接对象
            ConnectionTools tools = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = tools.getConnection();
            //获取dao对象，并传入连接
            CarDao cd = new CarDaoImpl(connection);
            //调用update方法，修改每日租金
            int result = cd.update("update car set car_putaway_status=? where car_id=?",putAwayStatus, id);
            //判断是否修改成功
            if(result>0){
                //调用本类中findCarById方法查询修改成功的数据并返回打印
                carList = findCarById(id, "汽车id");
            }else{
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList;
    }
    /**
     * 十、查看全部租车记录
     *
     * @return
     */
    @Override
    public List<Car> findRecord() {
        List<Car> carList=null;
        try {
            //获取连接工具类对象
            ConnectionTools tools = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = tools.getConnection();
            //获取dao对象，并将连接传入
            CarDao cd = new CarDaoImpl(connection);
            //调用query方法，查询c_record中的租出信息
            carList = cd.query("select record_id,cr.car_id,car_name,car_daily_rent,payment,car_remark," +
                    " brand_name,type_name,user_name,rent_time,return_time" +
                    " from c_record cr full join car c on cr.car_id=c.car_id" +
                    " full join c_brand cb on c.brand_id=cb.brand_id" +
                    " full join c_type ct on c.type_id=ct.type_id" +
                    " order by record_id" , rSet -> {
                List<Car> list = new ArrayList<>();
                while (rSet.next()) {
                    Car car = new Car();
                    car.setRecordId(rSet.getInt(TableInformationTools.Car.RECORD_ID));
                    car.setCarId(rSet.getInt(TableInformationTools.Car.CAR_ID));
                    car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                    car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                    car.setPayment(rSet.getDouble(TableInformationTools.Car.PAYMENT));
                    car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                    car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                    car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                    car.setUserName(rSet.getString(TableInformationTools.User.USER_NAME));
                    car.setRentTime(rSet.getString(TableInformationTools.Car.RENT_TIME));
                    car.setReturnTime(rSet.getString(TableInformationTools.Car.RETURN_TIME));
                    list.add(car);
                }
                return list;
            });
            for (int i = 0; i < carList.size(); i++) {
                if(carList.get(i).getRecordId()==0){
                    carList.remove(i);
                    i--;
                }
            }
            /*//关闭连接
            tools.closeConnection(connection);*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList;
    }

    /**
     * 十一、根据用户编号查看指定租车记录
     * @param id
     * @return
     */
    @Override
    public List<Car> findRecordByUserId(int id ) {
        String userName=null;
        List<Car> carList=null;
        try {
            //获取连接工具类对象
            ConnectionTools tools = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = tools.getConnection();
            UserDao ud = new UserDaoImpl(connection);
            //通过用户id 查找对应的用户名
            List<User>userList = ud.query("select user_name from c_user where user_id=?",rSet -> {
                List<User> list = new ArrayList<>();
                while (rSet.next()){
                    User user = new User();
                    user.setUserName(rSet.getString(TableInformationTools.User.USER_NAME));
                    list.add(user);
                }
                return list;
            },id);
            for (User user : userList) {
                userName = user.getUserName();
            }
            //获取dao对象，并将连接传入
            CarDao cd = new CarDaoImpl(connection);
            //调用query方法，查询c_record中的租出信息
            carList = cd.query("select record_id,cr.car_id,car_name,car_daily_rent,payment,car_remark," +
                    " brand_name,type_name,user_name,rent_time,return_time" +
                    " from c_record cr full join car c on cr.car_id=c.car_id" +
                    " full join c_brand cb on c.brand_id=cb.brand_id" +
                    " full join c_type ct on c.type_id=ct.type_id" +
                    " where user_name=?" +
                    " order by record_id", rSet -> {
                List<Car> list = new ArrayList<>();
                while (rSet.next()) {
                    Car car = new Car();
                    car.setRecordId(rSet.getInt(TableInformationTools.Car.RECORD_ID));
                    car.setCarId(rSet.getInt(TableInformationTools.Car.CAR_ID));
                    car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                    car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                    car.setPayment(rSet.getDouble(TableInformationTools.Car.PAYMENT));
                    car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                    car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                    car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                    car.setUserName(rSet.getString(TableInformationTools.User.USER_NAME));
                    car.setRentTime(rSet.getString(TableInformationTools.Car.RENT_TIME));
                    car.setReturnTime(rSet.getString(TableInformationTools.Car.RETURN_TIME));
                    list.add(car);
                }
                return list;
            },userName);
            /*//关闭连接
            tools.closeConnection(connection);*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList;
    }
    /**
     * 十一、根据汽车编号查看指定租车记录
     * @param id
     * @return
     */
    @Override
    public List<Car> findRecordByCarId(int id ) {
        List<Car> carList=null;
        try {
            //获取连接工具类对象
            ConnectionTools tools = ConnectionTools.GetConnection.INSTANCE.getInstance();
            //获取连接
            Connection connection = tools.getConnection();
            //获取dao对象，并将连接传入
            CarDao cd = new CarDaoImpl(connection);
            //调用query方法，查询c_record中的租出信息
            carList = cd.query("select record_id,cr.car_id,car_name,car_daily_rent,payment,car_remark," +
                    " brand_name,type_name,user_name,rent_time,return_time" +
                    " from c_record cr full join car c on cr.car_id=c.car_id" +
                    " full join c_brand cb on c.brand_id=cb.brand_id" +
                    " full join c_type ct on c.type_id=ct.type_id" +
                    " where cr.car_id=?" +
                    " order by record_id", rSet -> {
                List<Car> list = new ArrayList<>();
                while (rSet.next()) {
                    Car car = new Car();
                    car.setRecordId(rSet.getInt(TableInformationTools.Car.RECORD_ID));
                    car.setCarId(rSet.getInt(TableInformationTools.Car.CAR_ID));
                    car.setCarName(rSet.getString(TableInformationTools.Car.CAR_NAME));
                    car.setDailyRent(rSet.getDouble(TableInformationTools.Car.CAR_DAILY_RENT));
                    car.setPayment(rSet.getDouble(TableInformationTools.Car.PAYMENT));
                    car.setCarRemark(rSet.getString(TableInformationTools.Car.CAR_REMARK));
                    car.setBrand(rSet.getString(TableInformationTools.Car.BRAND_NAME));
                    car.setType(rSet.getString(TableInformationTools.Car.TYPE_NAME));
                    car.setUserName(rSet.getString(TableInformationTools.User.USER_NAME));
                    car.setRentTime(rSet.getString(TableInformationTools.Car.RENT_TIME));
                    car.setReturnTime(rSet.getString(TableInformationTools.Car.RETURN_TIME));
                    list.add(car);
                }
                return list;
            },id);
            /*//关闭连接
            tools.closeConnection(connection);*/
        } catch (Exception e) {
            e.printStackTrace();
        }
        return carList;
    }
}
